package com.bst.system.framework.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.bst.common.exception.base.BaseException;
import com.bst.common.exception.base.BaseRuntimeException;
import com.bst.common.utils.sql.EtlUtil;
import com.bst.common.vo.MetaDataVO;
import com.bst.base.domain.BaseDb;
import org.apache.commons.lang3.tuple.ImmutableTriple;
import org.apache.commons.lang3.tuple.Triple;
import org.springframework.boot.jdbc.DataSourceBuilder;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class DBUtil {

    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(
            DruidDataSource dataSource, String sql) throws BaseException {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        String qSQL = null;

        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            qSQL = EtlUtil.buildSql(sql, new Date(), new Date(), dataSource.getDbType());
            rs = statement.executeQuery(qSQL);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaData(rsMetaData);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new BaseException(String.format("获取查询:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", qSQL), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, connection);
        }
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaDataTo(DataSource ds, String tableName){
        Connection connection = null;
        try {
            connection = ds.getConnection();
            Triple<List<String>, List<Integer>, List<String>> columnMetaData = getColumnMetaData(connection, tableName, " * ");
            return columnMetaData;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.closeDBResources(null, null, connection);
        }
        return null;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, Triple<String, Integer, String>> getColumnMetaDataToMap(DataSource ds, String tableName) {
        Connection connection = null;
        try {
            connection = ds.getConnection();
            Map<String, Triple<String, Integer, String>> columnMetaDataToMap = getColumnMetaDataToMap(connection, tableName, " * ");
            return columnMetaDataToMap;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.closeDBResources(null, null, connection);
        }
        return null;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, Triple<String, Integer, String>> getColumnMetaDataToMap(
            Connection conn, String tableName, String column) {
        Triple<List<String>, List<Integer>, List<String>> columnMetaData = DBUtil.getColumnMetaData(conn, tableName, column);

        Map<String, Triple<String, Integer, String>> map = new HashMap<>();
        List<String> left = columnMetaData.getLeft();
        List<Integer> middle = columnMetaData.getMiddle();
        List<String> right = columnMetaData.getRight();
        for (int i = 0; i < left.size(); i++) {
            Triple<String, Integer, String> t = new ImmutableTriple(left.get(i), middle.get(i), right.get(i));
            map.put(left.get(i), t);
        }
        return map;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(
            Connection conn, String tableName, String column) {
        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = conn.createStatement();

            String queryColumnSql = "select " + column + " from " + tableName
                    + " where 1=2";


            rs = statement.executeQuery(queryColumnSql);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaData(rsMetaData);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new BaseRuntimeException(String.format("获取表:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", tableName), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, null);
        }
    }

    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(ResultSetMetaData rsMetaData) {

        Triple<List<String>, List<Integer>, List<String>> columnMetaData = new ImmutableTriple<>(
                new ArrayList<>(), new ArrayList<>(),
                new ArrayList<>());
        try {
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                columnMetaData.getLeft().add(rsMetaData.getColumnLabel(i + 1));
                columnMetaData.getMiddle().add(rsMetaData.getColumnType(i + 1));
                columnMetaData.getRight().add(rsMetaData.getColumnTypeName(i + 1));
            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new BaseRuntimeException(String.format("获取表字段的元信息时失败. 请联系 DBA 核查该库、表信息."), e);
        }
    }

    public static DruidDataSource createDataSource(BaseDb baseDb) throws InterruptedException {
        DruidDataSource datasource = DataSourceBuilder.create()
                .type(DruidDataSource.class)
                .driverClassName(baseDb.getDriver())
                .url(baseDb.getUrl())
                .username(baseDb.getNaUse())
                .password(baseDb.getPwd())
                .build();
        return datasource;
    }

    public static void closeDataSource(Object dataSource) {
        if (dataSource == null) return;
        if (dataSource.getClass().equals(DruidDataSource.class)) {
            ((DruidDataSource) dataSource).close();
        }
    }

    public static ResultSet query(Connection conn, String sql, int fetchSize, int queryTimeout)
            throws SQLException {
        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(fetchSize);
        stmt.setQueryTimeout(queryTimeout);
        return stmt.executeQuery(sql);
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, MetaDataVO> getTableColumnMetaDataToMetaDataVO(DruidDataSource ds, String tableName) throws BaseException {
        String queryColumnSql = "select * from " + tableName + " where 1=2";
        return getColumnMetaDataToMetaDataVO(ds, queryColumnSql);

    }

    /**
     * Map<ColumnName, MetaDataVO>
     *
     * @param dataSource
     * @param sql
     * @return
     * @throws BaseException
     */
    public static Map<String, MetaDataVO> getColumnMetaDataToMetaDataVO(
            DruidDataSource dataSource, String sql) throws BaseException {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        String qSQL = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            qSQL = EtlUtil.buildSql(sql, new java.util.Date(), new Date(), dataSource.getDbType());
            rs = statement.executeQuery(qSQL);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaDataToMetaDataVO(rsMetaData);
        } catch (SQLException e) {
            //e.printStackTrace();
            throw new BaseException(String.format("获取查询:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", qSQL), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, connection);
        }
    }

    /**
     * Map<ColumnName, MetaDataVO>
     *
     * @param rsMetaData
     * @return
     * @throws BaseException
     */
    public static Map<String, MetaDataVO> getColumnMetaDataToMetaDataVO(ResultSetMetaData rsMetaData) throws BaseException {

        Map<String, MetaDataVO> columnMetaData = new LinkedHashMap<>();
        try {
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                MetaDataVO metaDataVO = new MetaDataVO();
                metaDataVO.setColumnName(rsMetaData.getColumnLabel(i + 1).toUpperCase());
                metaDataVO.setColumnType(rsMetaData.getColumnType(i + 1));
                metaDataVO.setColumnTypeName(rsMetaData.getColumnTypeName(i + 1));
                metaDataVO.setColumnPrecision(rsMetaData.getPrecision(i + 1));
                metaDataVO.setColumnScale(rsMetaData.getScale(i + 1));
                columnMetaData.put(metaDataVO.getColumnName(), metaDataVO);

            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new BaseException(String.format("获取表字段的元信息时失败. 请联系 DBA 核查该库、表信息."), e);
        }
    }

    public static void closeDBResources(ResultSet rs, Statement stmt,
                                        Connection conn) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException unused) {
            }
        }

        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException unused) {
            }
        }

        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException unused) {
            }
        }
    }
}
